/*This file was used to insert records into our database*/

import java.sql.*;
import org.apache.derby.jdbc.ClientDriver;
import java.util.Scanner;
import java.io.*;

public class CreateRecords {
	public static void main(String[] args) {
		//setting input stream
		Scanner fileInput = null;
		try {
			//change input file as needed
			fileInput = new Scanner(new FileInputStream("./input/movie.txt"));			
		} catch (FileNotFoundException e1) {
			System.out.println("Input file not found.");
			System.exit(1);
		}
		
		String line = "", title = "", director = "", actor1 = "", actor2 = "", actor3 = "", genre = "", rating = "";
		String year = "";
		int begin = 0, last = 0;
		int movieId = 0, actorId = 0, directorId = 0;
		
		Connection conn = null;
		Statement stmt;
		String qry;
		try {
			// Step 1: connect to database server
			Driver d = new ClientDriver();
			String url = "jdbc:derby://localhost:4567/moviedb";
			conn = d.connect(url, null);

			//Read input file
			while (fileInput.hasNextLine()) {				
				line = fileInput.nextLine();

				String[] s= line.split(",");
				title = s[0];
				actor1 = s[5].substring(1);
				actor2 = s[6].substring(1);
				actor3 = s[7].substring(1);
				//System.out.println(title + "," + actor1 + "," + actor2 + "," + actor3);
				
				stmt = conn.createStatement();
				qry = "SELECT MOVIEID FROM MOVIE WHERE TITLE = '" 
						+ title + "'";
				//System.out.println(qry);
				ResultSet rs = stmt.executeQuery(qry);				
				while (rs.next()) {
					movieId = rs.getInt("MovieId");
				}			
				rs.close();
				//System.out.println(title + " " + movieId);				
				
				//actor1
				begin = actor1.indexOf(" ");
				String fName = actor1.substring(0, begin).trim();
				String lName = actor1.substring(begin+1).replace(' ', '\0');
				//System.out.print(lName + "," + fName);
				
				stmt = conn.createStatement();
				qry = "SELECT ACTORID FROM ACTOR WHERE FNAME = '" 
						+ fName + "' AND LNAME = '" + lName + "'";
				//System.out.println(qry);
				rs = stmt.executeQuery(qry);
				
				while (rs.next()) {
					actorId = rs.getInt("ActorId");
				}			
				rs.close();
				
				//System.out.print(actorId + "\n");
				
				qry = "INSERT INTO ROLE(MOVIEID, ACTORID) VALUES("
						+ movieId + ", " + actorId + ")";
				//System.out.println(qry + " " + title + actor1);
				stmt.executeUpdate(qry);

				//actor2
				begin = actor2.indexOf(" ");
				fName = actor2.substring(0, begin).trim();
				lName = actor2.substring(begin+1).replace(' ', '\0');
				//System.out.print(lName + "," + fName);
				
				stmt = conn.createStatement();
				qry = "SELECT ACTORID FROM ACTOR WHERE FNAME = '" 
						+ fName + "' AND LNAME = '" + lName + "'";
				//System.out.println(qry);
				rs = stmt.executeQuery(qry);
				
				while (rs.next()) {
					actorId = rs.getInt("ActorId");
				}			
				rs.close();
				
				//System.out.print(actorId + "\n");
				
				qry = "INSERT INTO ROLE(MOVIEID, ACTORID) VALUES("
						+ movieId + ", " + actorId + ")";
				//System.out.println(qry + " " + title + actor2);
				stmt.executeUpdate(qry);
				
				//actor3
				begin = actor3.indexOf(" ");
				fName = actor3.substring(0, begin).trim();
				lName = actor3.substring(begin+1).replace(' ', '\0');
				//System.out.print(lName + "," + fName);
				
				stmt = conn.createStatement();
				qry = "SELECT ACTORID FROM ACTOR WHERE FNAME = '" 
						+ fName + "' AND LNAME = '" + lName + "'";
				//System.out.println(qry);
				rs = stmt.executeQuery(qry);
				
				while (rs.next()) {
					actorId = rs.getInt("ActorId");
				}			
				rs.close();
				
				//System.out.print(actorId + "\n");
				
				qry = "INSERT INTO ROLE(MOVIEID, ACTORID) VALUES("
						+ movieId + ", " + actorId + ")";
				//System.out.println(qry + " " + title + actor3);
				stmt.executeUpdate(qry);
					
				
				//MOVIE & DIRECTOR linking
//				String[] s= line.split(",");
//				title = s[0];
//				director = s[4].substring(1);
//								
//				begin = director.indexOf(" ");
//				String fName = director.substring(0, begin);
//				String lName = director.substring(begin+1);
//				//System.out.println(lName + ", " + fName);
//				
//				stmt = conn.createStatement();
//				qry = "SELECT DIRECTORID FROM DIRECTOR WHERE FNAME = '" 
//						+ fName + "' AND LNAME = '" + lName + "'";
//				//System.out.println(qry);
//				ResultSet rs = stmt.executeQuery(qry);
//				
//				while (rs.next()) {
//					directorId = rs.getInt("DirectorID");
//				}			
//				rs.close();
//				
//				//System.out.println(directorId);
//				
//				qry = "UPDATE MOVIE "
//						+ "SET DIRECTORID = " + directorId
//						+ " WHERE TITLE = '" + title + "'";
//				//System.out.println(qry);
//				stmt.executeUpdate(qry);

				//MOVIE INSERT
//				String[] s= line.split(", ");
//				title = s[0];
//				year = s[1];
//				genre = s[2];
//				rating = s[3];
//				qry = "INSERT INTO MOVIE(TITLE, MYEAR, GENRE, WRITERID, DIRECTORID, STUDIOID, RATING) VALUES ('" 
//						+ title + "', "
//						+ year + ", '"
//						+ genre + "', "
//						+ 1 + ", "
//						+ 1 + ", "
//						+ 1 + ", '"
//						+ rating + "'"
//						+ ")";
//				stmt = conn.createStatement();
//				stmt.executeUpdate(qry);
				
				//ACTOR INSERT
//				String[] s = line.split(" ");
//				String fName = s[0];
//				String lName = "";
//				for (String a : s) {
//					if (a != s[0])
//						lName += a;
//				}
//				stmt = conn.createStatement();
//				qry = "INSERT INTO ACTOR(FNAME, LNAME) VALUES ('" + fName + "', '" + lName + "')";
//				//System.out.println(qry);
//				stmt.executeUpdate(qry);				
								
				//DIRECTOR INSERT
//				begin = line.indexOf(" ");
//				String fName = line.substring(0, begin);
//				String lName = line.substring(begin+1);
//				stmt = conn.createStatement();
//				qry = "INSERT INTO DIRECTOR(FNAME, LNAME) VALUES ('" + fName + "', '" + lName + "')";
//				//System.out.println(qry);
//				stmt.executeUpdate(qry);
				
//				//GENRE INSERT
//				String[] splittedLine = line.split("\n"); 
//				for (String a : splittedLine) {
//					//System.out.printf("%s", a);										 		
//					stmt = conn.createStatement();
//					qry = "INSERT INTO GENRE VALUES ('" + a + "')";
//					stmt.executeUpdate(qry);
//				}
			}	
		}
		
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		finally {
			// Step 4: Disconnect from the server
			try {
				if(conn != null)
					conn.close();
			}
			catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}
}